//
//  SQLiteHelper.m
//  Etion
//
//  Created by  user on 11-3-21.
//  Copyright 2011 GuangZhouXuanWu. All rights reserved.
//

#import "XMSSQLiteHelper.h"

#import "NSStringExtend.h"

//extern int sqlite3_key(sqlite3 *db,const void *pKey, int nKey);

//extern int sqlite3_rekey(sqlite3 *db,const void *pKey, int nKey);

typedef int(*_ExceCallBack)(void *, int, char **, char **);

@implementation XMSSQLiteHelper

@synthesize m_sqlhandle;
//@synthesize m_lock;

- (id)initWithSQLHandler:(sqlite3 *)sqlhandle
{
    self = [super init];
    m_sqlhandle = sqlhandle;
    m_bHandler = YES;
    return self;
}

- (id)initWithSQLiteHelper:(XMSSQLiteHelper *)sqlitehelper
{
    self = [super init];
    m_sqlhandle = sqlitehelper.m_sqlhandle;
    m_bHandler = YES;
    return self;
}

- (NSInteger)createTable:(const char *)szSQL path:(NSString *)szPath
{
    return [self createTable:szSQL psw:NULL path:szPath];
}

- (NSInteger)createTable:(const char *)szSQL psw:(const char *)szPsw path:(NSString *)szPath
{
    NSInteger nResult = -1;
    sqlite3 *handle = NULL;
    if (SQLITE_OK == sqlite3_open([szPath UTF8String], &handle))
    {
        if (szPsw != NULL)
            ;//sqlite3_key(handle, szPsw, ToInt strlen(szPsw));
        sqlite3_stmt *statement = NULL;
        nResult = sqlite3_prepare_v2(handle, szSQL, -1, &statement, NULL);
        if (nResult == SQLITE_OK)
        {
            nResult = sqlite3_step(statement);
            sqlite3_finalize(statement);
        }
    }

    sqlite3_close(handle);
    return nResult;
}

- (NSInteger)openTable:(NSString *)szPath flags:(NSInteger)flags
{
    return [self openTable:szPath psw:NULL flags:flags];
}

- (NSInteger)openTable:(NSString *)szPath psw:(const char *)szPsw flags:(NSInteger)flags
{
    NSInteger nResult = -1;
    nResult = sqlite3_open_v2([szPath UTF8String], &m_sqlhandle, ToInt flags, NULL);
    if (nResult != SQLITE_OK)
        [self closeTable];
    else
    {
        if (szPsw != NULL)
            ;//sqlite3_key(m_sqlhandle, szPsw, ToInt strlen(szPsw));
    }
    return nResult;
}

- (NSInteger)openTable:(sqlite3 *)sqlhandle
{
    m_sqlhandle = sqlhandle;
    m_bHandler = YES;
    return SQLITE_OK;
}

- (void)closeTable
{
    if (NO == m_bHandler)
    {
        sqlite3_close(m_sqlhandle);
    }
    m_sqlhandle = NULL;
    m_bHandler = NO;
}

- (NSInteger)modifyDBPassword:(NSString *)szPath oldPsw:(const char *)szOldpsw newPsw:(const char *)szNewpsw
{
    NSInteger nResult = -1;
    sqlite3 *handle = NULL;
    if (SQLITE_OK == sqlite3_open([szPath UTF8String], &handle))
    {
        //sqlite3_key(handle, szOldpsw, ToInt strlen(szOldpsw));
        //sqlite3_rekey(handle, szNewpsw, ToInt strlen(szNewpsw));
    }

    sqlite3_close(handle);
    return nResult;
}

- (NSInteger)immediateExecSQL:(const char *)szSQL
{
    return [self immediateExecSQL:szSQL changerows:NULL];
}

- (NSInteger)immediateRunSQL:(const char *)szSQL
{
    NSInteger nResult= [self immediateExecSQL:szSQL changerows:NULL];
    return nResult==SQLITE_DONE?YES:nResult;
}

- (NSInteger)immediateExecSQL:(const char *)szSQL changerows:(NSUInteger *)nRow
{
    NSInteger nResult = -1;
    nResult = [self prepareExecSQL:szSQL];
    if (SQLITE_OK == nResult)
        nResult = [self execSQL];
    if (SQLITE_DONE == nResult && nRow != NULL)
        *nRow = [self changeRows];
    return nResult;
}

- (NSInteger)immediateRunSQL:(const char *)szSQL changerows:(NSUInteger *)nRow
{
    NSInteger nResult= [self immediateExecSQL:szSQL changerows:nRow];
    return nResult == SQLITE_DONE ? YES : nResult;
}

- (NSInteger)prepareExecSQL:(const char *)szSQL
{
    NSInteger nResult = FALSE;
    nResult = sqlite3_prepare_v2(m_sqlhandle, szSQL, -1, &m_statement, NULL);
    return nResult;
}

- (NSInteger)execSQL
{
    NSInteger nResult = FALSE;
    nResult = [self execStatement];
    [self finalizeStatement];
    return nResult;
}

- (NSInteger)runSQL
{
    NSInteger nResult = FALSE;
    nResult = [self execStatement];
    [self finalizeStatement];
    return nResult == SQLITE_DONE ? YES : NO;;
}

- (void)finalizeStatement
{
    sqlite3_finalize(m_statement);
    m_statement = NULL;
}

- (NSInteger)execStatement
{
    NSInteger nResult = sqlite3_step(m_statement);
    return nResult;
}

- (BOOL)selectRowContinue
{
    return SQLITE_ROW == [self execStatement];
}

- (BOOL)selectRowContinue:(NSInteger *)nResult
{
    NSInteger n = [self execStatement];
    *nResult = n;
    return SQLITE_ROW == n;
}

- (NSInteger)changeRows
{
    return sqlite3_changes(m_sqlhandle);
}

//-(int)GetColumns:(NSMutableArray**)arCol
//{
//	int nResult=0;
//	int n=sqlite3_column_count(m_statement);
//	if(n>0)
//	{
//		nResult=YES;
//		(*arCol)=[[NSMutableArray alloc]initWithCapacity:n];
//		for(int i=0;i<n;i++)
//		{
//			[(*arCol) addObject:[NSString stringWithUTF8String:sqlite3_column_name(m_statement,i)]];
//		}
//	}
//	return nResult;
//}

- (NSInteger)getColumnCount
{
    return sqlite3_column_count(m_statement);
}

- (NSInteger)getColumnType:(NSUInteger)nIndex
{
    return sqlite3_column_type(m_statement, ToInt nIndex);
}

- (NSInteger)exec:(void (^)())function
{
    //	char* error=NULL;
    sqlite3_exec(m_sqlhandle, "begin;", NULL, NULL, NULL);
    function();
    //	error=NULL;
    sqlite3_exec(m_sqlhandle, "commit;", NULL, NULL, NULL);
    //	sqlite3_exec(m_sqlhandle,"rollback;",NULL,NULL,&error);
    return 1;
}

//返回1正常
- (NSInteger)transExce:(NSInteger (^)())function
{
    sqlite3_exec(m_sqlhandle, "begin;", NULL, NULL, NULL);
    NSInteger nResult = function();
    if (YES == nResult)
        sqlite3_exec(m_sqlhandle, "commit;", NULL, NULL, NULL);
    else
        sqlite3_exec(m_sqlhandle, "rollback;", NULL, NULL, NULL);

    return nResult;
}

- (NSInteger)exec2:(NSInteger (^)())function
{
//	char* error=NULL;
    sqlite3_exec(m_sqlhandle, "begin;", NULL, NULL, NULL);
    NSInteger nResult = function();
//	error=NULL;
    if (SQLITE_DONE == nResult)
        sqlite3_exec(m_sqlhandle, "commit;", NULL, NULL, NULL);
    else
        sqlite3_exec(m_sqlhandle, "rollback;", NULL, NULL, NULL);

    return nResult;
}

- (NSInteger)exec:(const char *)szSQL callBack:(ExecCallBack)callback
{
//    char* error=NULL;
    _ExceCallBack excecallback = NULL;
    if (callback != NULL)
    {
        Block_release(m_execcallback);
        m_execcallback = Block_copy(callback);
        excecallback = _ExecCallBackFun;
    }
    return sqlite3_exec(m_sqlhandle, szSQL, excecallback, self, NULL);
}

- (ExecCallBack)m_execcallback
{
    return m_execcallback;
}

static int _ExecCallBackFun(void *param, int nIndex, char **szColvalue, char **szColname)
{
    @autoreleasepool
    {
        XMSQueueDictionary *qd = [[XMSQueueDictionary new] autorelease_mm];
        for (NSUInteger i = 0; i < nIndex; i++)
        {
            char *szKey = szColname[i];
            char *szValue = szColvalue[i];
            XMSKvPair *pair = [XMSKvPair kvPairWithKey:[NSString stringWithCString:szKey encoding:NSUTF8StringEncoding] value:[NSString stringWithCString:szValue encoding:NSUTF8StringEncoding]];
            [qd addKvPairDirect:pair];
        }
        NSInteger nResult = [((XMSSQLiteHelper *) param) m_execcallback](qd);
        
        
        return ToInt nResult;
    }
}

- (NSInteger)getTable:(const char *)szSQL gettingTable:(void (^)(NSUInteger nRow, NSUInteger nCol, NSString *szColname, NSString *szColvalue))gettable
{
    char *error = NULL;
    char **result = NULL;
    int nRow = 0;
    int nCol = 0;
    NSInteger nResult = sqlite3_get_table(m_sqlhandle, szSQL, &result, &nRow, &nCol, &error);
    for (NSUInteger i = 0; i < nRow; i++)
    {
        for (NSUInteger j = 0; j < nCol; j++)
        {
            gettable(i, j, [NSString stringWithCString:result[j] encoding:NSUTF8StringEncoding], [NSString stringWithCString:result[j + nCol + nCol * i] encoding:NSUTF8StringEncoding]);
        }
    }
    sqlite3_free_table(result);
    return nResult;
}

- (NSInteger)selectTable:(const char *)szSQL gettingTable:(void (^)(BOOL *bStop))gettable
{
    NSInteger nResult = FALSE;
    BOOL bStop=NO;
    [self prepareExecSQL:szSQL];
    while ([self selectRowContinue:&nResult])
    {
        gettable(&bStop);
        if(bStop==YES)
            break;
    }
    [self finalizeStatement];
    return (nResult == SQLITE_DONE|| nResult == SQLITE_OK|| nResult == SQLITE_ROW);
}

- (NSString *)getColumnName:(NSUInteger)nIndex
{
    return [NSString stringWithUTF8String:sqlite3_column_name(m_statement, ToInt nIndex)];
}

- (void)putFieldNull:(NSUInteger)nIndex
{
    sqlite3_bind_null(m_statement, ToInt nIndex);
}

- (void)putFieldInt:(NSUInteger)nIndex param:(NSInteger)param
{
    sqlite3_bind_int(m_statement, ToInt nIndex, ToInt param);
}

- (NSInteger)getFieldInt:(NSUInteger)nIndex
{
    return sqlite3_column_int(m_statement, ToInt nIndex);
}

- (NSNumber*)getFieldNSNumberInt:(NSUInteger)nIndex
{
    return [NSNumber numberWithInteger:[self getFieldInt:nIndex]];
}

- (void)putFieldLongLong:(NSUInteger)nIndex param:(long long)param
{
    sqlite3_bind_int64(m_statement, ToInt nIndex, param);
}

- (long long)getFieldLongLong:(NSUInteger)nIndex
{
    return sqlite3_column_int64(m_statement, ToInt nIndex);
}

- (NSNumber*)getFieldNSNumberLongLong:(NSUInteger)nIndex
{
    return [NSNumber numberWithLongLong:[self getFieldLongLong:nIndex]];
}

- (void)putFieldDouble:(NSUInteger)nIndex param:(double)param
{
    sqlite3_bind_double(m_statement, ToInt nIndex, param);
}

- (double)getFieldDouble:(NSUInteger)nIndex
{
    return sqlite3_column_double(m_statement, ToInt nIndex);
}

- (void)putFieldNSString:(NSUInteger)nIndex param:(NSString *)param
{
    sqlite3_bind_text(m_statement, ToInt nIndex, [param UTF8String], -1, SQLITE_TRANSIENT);
}

- (NSString *)getFieldNSString:(NSUInteger)nIndex
{
    char *ch = (char *) sqlite3_column_text(m_statement, ToInt nIndex);
    return [NSString stringWithUTF8String:nil!= ch ? ch : ""];
}

- (void)putFieldGUID:(NSUInteger)nIndex param:(GUID)param
{
    [self putFieldByte:nIndex param:(Byte *) &param length:16];
}

- (GUID)getFieldGUID:(NSUInteger)nIndex
{
    GUID g = {0};
    NSUInteger nLen = 0;
    Byte *b = [self getFieldByte:nIndex length:&nLen];
    memcpy(&g, b, nLen);
    return g;
}

- (void)putFieldByte:(NSUInteger)nIndex param:(Byte *)param length:(NSUInteger)nLength
{
    sqlite3_bind_blob(m_statement, ToInt nIndex, param, ToInt nLength, SQLITE_TRANSIENT);
}

- (Byte *)getFieldByte:(NSUInteger)nIndex length:(NSUInteger *)nLength
{
    (*nLength) = sqlite3_column_bytes(m_statement, ToInt nIndex);
    return (Byte *) sqlite3_column_blob(m_statement, ToInt nIndex);
}

- (void)putFieldNSDate:(NSUInteger)nIndex param:(NSDate *)datetime
{
    [self putFieldDouble:nIndex param:[datetime timeIntervalSince1970]];
}

- (NSDate *)getFieldNSDate:(NSUInteger)nIndex
{
    double t = [self getFieldDouble:nIndex];
    if(t <=0 )
        return nil;
    return [NSDate dateWithTimeIntervalSince1970:t];
}

- (void)putFieldNSData:(NSUInteger)nIndex param:(NSData *)param
{
    [self putFieldByte:nIndex param:(Byte*)param.bytes length:param.length];
}

- (NSData*)getFieldNSData:(NSUInteger)nIndex
{
    NSUInteger nLen = 0;
    Byte* byte=[self getFieldByte:nIndex length:&nLen];
    return [NSData dataWithBytes:byte length:nLen];
}

- (XMSQueueDictionary *)getTableHeads:(NSString *)szTable
{
    XMSQueueDictionary *qd = nil;
    NSString *sql = [NSString stringWithFormat:@"select sql from sqlite_master where type='table' and name='%@'", szTable];
    NSInteger nResult = [self prepareExecSQL:[sql UTF8String]];
    if (SQLITE_OK == nResult)
    {
        if (YES == [self selectRowContinue])
        {
            NSString *szHead = [self getFieldNSString:0];

            NSMutableCharacterSet *set = [NSMutableCharacterSet new];
            [set addCharactersInString:@"("];
            [set addCharactersInString:@")"];
            NSArray *ar = [szHead componentsSeparatedByCharactersInSet:set];
            [set release_mm];
            ar = [((NSString *) [ar objectAtIndex:1]) componentsSeparatedByString:@","];
            if (ar.count > 0)
            {
                qd = [XMSQueueDictionary new];
                for (NSString *s in ar)
                {
                    s = [s stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
                    NSArray *a = [s componentsSeparatedByString:@" "];
                    if (a.count >= 2)
                    {
                        NSString *key = [a objectAtIndex:0];
                        key = [key stringByTrimmingCharactersInSet:[NSCharacterSet controlCharacterSet]];
                        NSString *value = [a objectAtIndex:1];
                        value = [value stringByTrimmingCharactersInSet:[NSCharacterSet controlCharacterSet]];
                        [qd addKey:key value:value];
                    }
                }
            }

        }
    }
    [self finalizeStatement];
    return [qd autorelease_mm];
}

+ (NSString *)replaceSpecialString:(NSString *)szStr
{
    NSMutableDictionary *dic = [[NSMutableDictionary new] autorelease_mm];
    [dic setObject:@"//" forKey:@"/"];
    [dic setObject:@"''" forKey:@"'"];
    [dic setObject:@"/[" forKey:@"["];
    [dic setObject:@"/]" forKey:@"]"];
    [dic setObject:@"/%" forKey:@"%"];
    [dic setObject:@"/&" forKey:@"&"];
    [dic setObject:@"/_" forKey:@"_"];
    [dic setObject:@"/(" forKey:@"("];
    [dic setObject:@"/)" forKey:@")"];
    return [NSString replaceString:szStr replacekv:dic];
}

- (void)dealloc
{
//    [m_lock release];
    if (m_execcallback != NULL)
    Block_release(m_execcallback);
    superdealloc_mm;
}

@end
